// **********************************************************************************************
// Este archivo fuente es propiedad de Feria de Remates S.R.L. de C.V. de C.V. Prohibida su copia
// o uso sin autorización explícita.
// This source file is propietary to Feria de Remates. May not be used or copied
// without explicit authorization.
// **********************************************************************************************/
package com.feriaderemates.dao.user;

import com.feriaderemates.domain.userAccount.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

/**
 *
 * Singleton Dao class. Implements user operations.
 *
 * @author Oscar Garcia
 */
@Slf4j
@Service("userDao")
public class UserDao extends JdbcDaoSupport implements UserDaoInterface {
 
   
    private static final UserDao userDao = new UserDao();
    // Querys
    private static String INSERT_USER = "INSERT INTO portal.user (first_name, last_name, hashed_password, user_account_status, username, "
            + " user_account_type, registration_confirmation_code, registration_date) "
            + " VALUES (?, ?, ?, ?, ?, ?, ?, NOW())";

    private static String GET_USER_BY_USERNAME= "Select * from portal.user where username  = ?";
    private static String GET_USER_BY_EMAIL= "Select * from portal.user where email  = ?";
    private static String SET_USER_REOVERY_CODE = "update portal.user set password_recovery_code = ? where email = ?";
    private static String LAST_PWD_RESET_REQ = "select last_recovery_code_request from portal.user  where email = ?";
    private static String UPDATE_USER = "update portal.user set first_name = ?, last_name = ?,second_last_name =?"
      + " , email = ?, cell_phone =?, username = ? ,"
     + "  user_account_status = ? ,user_account_type = ?"
      + " where user_id = ?";
    private static String UPDATE_USER_PASSWORD ="UPDATE portal.user SET hashed_password = ?, last_recovery_code_request = NOW() "
            + " WHERE password_recovery_code = ? ";
   
    /**
     * Factory method.
     *
     * @return the only one instance of this class
     */
    static UserDao getInstance() {
        return userDao;
    }

    private UserDao() {
    }
	
    /**
     * Register a new User
     *
     * @param user the user to register.
     * @param registrationConfirmationCode code use to verify user's email after registration
     * @return the unique User id returned after the insert to the database.
     */
	
    public int registerUser(final User user, final String registrationConfirmationCode) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(INSERT_USER, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, user.getFirstName());
                ps.setString(2, user.getLastName());
                ps.setString(3, user.getHashedPassword());
                ps.setString(4, user.getUserAccountStatus().name());
                ps.setString(5, user.getUsername());
                ps.setString(6, user.getAccountType().name());
                ps.setString(7, registrationConfirmationCode);
                return ps;
            }
        }, keyHolder);
        Object key = keyHolder.getKeys().get("user_id");
        return (Integer) key;
    }

    /**
     * Gets a user details by his username
     * @param username the username
     * @return the User instance
     */
    public User getUserByUsername(String username) {
        return (User)getJdbcTemplate().query(GET_USER_BY_USERNAME,new Object[]{username}, new UserRowMapper()).get(0);
    }
    
    /**
     * Gets a user by his email
     * @param email the user's email
     * @return the User instance
     */

    public User getUserByEmail(String email) {
        return (User)getJdbcTemplate().queryForObject(GET_USER_BY_EMAIL,new Object[]{email}, new UserRowMapper());
    }
    
    /**
     * Updates basic user's information. Updates all the fields except
     * the password and the id.
     * @param user the user to update
     */
    public void updateUser(final User user) {
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
               PreparedStatement ps = connection.prepareStatement(UPDATE_USER);
               ps.setString(1, user.getFirstName());
               ps.setString(2, user.getLastName());
               ps.setString(3, user.getSecondLastName());
               ps.setString(4, user.getEmail());
               ps.setString(5, user.getCellPhone());
               ps.setString(6, user.getUsername());
               ps.setString(7, user.getUserAccountStatus().name());
               ps.setString(8, user.getAccountType().name());
               ps.setInt(9, user.getUserId());
               return ps;
            }
        });
    }
    
    /**
     * Updates user's password and sets the field last_recovery_code_request to NOW()
     * @param newPassword the new password
     * @param recoveryCode the recoveryCode to find the user account
     */
    public void updateUserPassword(final String newPassword, final String recoveryCode) {
       
         getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
               PreparedStatement ps = connection.prepareStatement(UPDATE_USER_PASSWORD);
               ps.setString(1, newPassword);
               ps.setString(2, recoveryCode);
               
               return ps;
            }
        });
    }
    
    /**
     * Gets the last time when the user requested to reset his password.
     * @param email user's email
     * @return the Timestamp
     */
    public Timestamp getLastTimeUserRequestedResetPassword(String email) {
       return (Timestamp) getJdbcTemplate().queryForObject(LAST_PWD_RESET_REQ, new Object[]{email},Timestamp.class);
    }

    /**
     * Set's a recoveryCode in the user table.
     * @param email the user's email
     * @param recoveryCode the code to set
     */
    public void setUsersRecoveryCode(final String email, final String recoveryCode) {
       getJdbcTemplate().update(SET_USER_REOVERY_CODE, new Object[]{recoveryCode,email});
    }
};
